﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
using bdxyit.com.RecruitmentAppSys.Models;


/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：WorkListInfoModels
*系统时间：3/25/2012 3:39:33 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:39:33 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class WorkListInfoDAL : IWorkListInfoDAL
    {
        private const string SQL_SELECT_WORKLISTINFO = " select ID,StartTime,EndTime,Job,UserID "
                                                    + "  FROM WorkListInfoes ";

        private const string SQL_DELETE_WORKLISTINFO_BYUSERID = " delete from WorkListInfoes "
                                                    + "  where UserID=@UserID";

        private const string SQL_UPDATE_WORKLISTINFO_BYUSERID = " update WorkListInfoes set "
                                                    + " StartTime=@StartTime,"
                                                    + " EndTime=@EndTime,"
                                                    + " Job=@Job,"
                                                    + " UserID=@UserID"
                                                    + " where UserID=@UserID";

        private const string SQL_INSERT_WORKLISTINFO = " insert into WorkListInfoes("
                                                    + " ID,StartTime,EndTime,Job,UserID)"
                                                    + "  values ("
                                                    + " @ID,@StartTime,@EndTime,@Job,@UserID)";

        private const string FIELD_ID = "@ID";
        private const string FIELD_StartTime = "@StartTime";
        private const string FIELD_EndTime = "@EndTime";
        private const string FIELD_Job = "@Job";
        private const string FIELD_UserID = "@UserID";


        /// <summary>
        /// 获取所有worklistinfo信息
        /// </summary>
        /// <returns></returns>
        public List<WorkListInfo> SelectWorkListInfo()
        {
            List<WorkListInfo> WorkListInfo = new List<WorkListInfo>();

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_WORKLISTINFO))
            {
                if (rdr.Read())
                {
                    WorkListInfo fi = new WorkListInfo();
                    fi.ID = int.Parse(rdr["ID"].ToString());
                    fi.StartTime = DateTime.Parse(rdr["StartTime"].ToString());
                    fi.EndTime = DateTime.Parse(rdr["EndTime"].ToString());
                    fi.Job = rdr["Job"].ToString();
                    fi.UserID = rdr["UserID"].ToString();
                    WorkListInfo.Add(fi);
                }
            }
            return WorkListInfo;
        }

        /// <summary>
        /// 删除worklistinfo信息根据userid
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool DeleteWorkListInfoByUserId(string UserId)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 18);
            parm.Value = UserId;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_WORKLISTINFO_BYUSERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 插入一条记录给workinfolist
        /// </summary>
        /// <param name="workinfo"></param>
        /// <returns></returns>
        public bool InsertWorkInfo(WorkListInfo workinfo)
        {
            bool flag = false;

            SqlParameter[] WorkListInfoParms = GetWorkListInfoParameters();
            SqlCommand cmd = new SqlCommand();
            WorkListInfoParms[0].Value = workinfo.ID;
            WorkListInfoParms[1].Value = workinfo.StartTime;
            WorkListInfoParms[2].Value = workinfo.EndTime;
            WorkListInfoParms[3].Value = workinfo.Job;
            WorkListInfoParms[4].Value = workinfo.UserID;

            foreach (SqlParameter parm in WorkListInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_WORKLISTINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        public bool UpdateWorkListInfo(WorkListInfo cmodel)
        {
            bool flag = false;
            SqlParameter[] WorkListInfoParms = GetWorkListInfoParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            WorkListInfoParms[0].Value = cmodel.ID;
            WorkListInfoParms[1].Value = cmodel.StartTime;
            WorkListInfoParms[2].Value = cmodel.EndTime;
            WorkListInfoParms[3].Value = cmodel.Job;
            WorkListInfoParms[4].Value = cmodel.UserID;

            foreach (SqlParameter parm in WorkListInfoParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_WORKLISTINFO_BYUSERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }



        /// <summary>
        /// 获取所有列类型
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetWorkListInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                	new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_StartTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_EndTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_Job, SqlDbType.VarChar,200),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}